package com.example.teatea.dao;

import com.example.teatea.project.Good;
import com.example.teatea.project.Member;
import com.example.teatea.project.Staff;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class FindAll {
    public List<Staff> getAllStaff(){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            // 1.加载驱动
            // 2.连接数据库
            Properties info=new Properties();
            info.load(new FileInputStream("src\\main\\resources\\com\\example\\teatea\\sql.properties"));
            String user=info.getProperty("user");
            String password=info.getProperty("password");
            String Driver=info.getProperty("Driver");
            String url=info.getProperty("url");
            Class.forName(Driver);
            conn= DriverManager.getConnection(url,user,password);
            // 3.创建语句
            st = conn.createStatement();
            String sql = "select * from staff where sState = 1 and sType = 0";
//            System.out.println(sql);
            // 4.执行语句
            rs = st.executeQuery(sql);
            // 创建一个集合
            List<Staff> list = new ArrayList<Staff>();
            while (rs.next()) {
                Staff stu = new Staff(
                        rs.getString("sName"),
                        rs.getString("sSex"),
                        rs.getString("sPhone"),
                        rs.getString("sTime"));
                list.add(stu);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Member> getAllMember(){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            // 1.加载驱动
            // 2.连接数据库
            Properties info=new Properties();
            info.load(new FileInputStream("src\\main\\resources\\com\\example\\teatea\\sql.properties"));
            String user=info.getProperty("user");
            String password=info.getProperty("password");
            String Driver=info.getProperty("Driver");
            String url=info.getProperty("url");
            Class.forName(Driver);
            conn= DriverManager.getConnection(url,user,password);
            // 3.创建语句
            st = conn.createStatement();
            String sql = "select * from members";
//            System.out.println(sql);
            // 4.执行语句
            rs = st.executeQuery(sql);
            // 创建一个集合
            List<Member> list = new ArrayList<Member>();
            while (rs.next()) {
                Member stu = new Member(
                        rs.getString("mName"),
                        rs.getString("mSex"),
                        rs.getString("mBirthday"),
                        rs.getString("mPhone"),
                        rs.getString("mTime"));
                list.add(stu);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Staff> getRegisterStaff() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            // 1.加载驱动
            // 2.连接数据库
            Properties info=new Properties();
            info.load(new FileInputStream("src\\main\\resources\\com\\example\\teatea\\sql.properties"));
            String user=info.getProperty("user");
            String password=info.getProperty("password");
            String Driver=info.getProperty("Driver");
            String url=info.getProperty("url");
            Class.forName(Driver);
            conn= DriverManager.getConnection(url,user,password);
            // 3.创建语句
            st = conn.createStatement();
            String sql = "select * from staff where sState = 0";
//            System.out.println(sql);
            // 4.执行语句
            rs = st.executeQuery(sql);
            // 创建一个集合
            List<Staff> list = new ArrayList<Staff>();
            while (rs.next()) {
                Staff stu = new Staff(
                        rs.getString("sName"),
                        rs.getString("sSex"),
                        rs.getString("sPhone"));
                list.add(stu);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Good> getAllGoods(){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            // 1.加载驱动
            // 2.连接数据库
            Properties info=new Properties();
            info.load(new FileInputStream("src\\main\\resources\\com\\example\\teatea\\sql.properties"));
            String user=info.getProperty("user");
            String password=info.getProperty("password");
            String Driver=info.getProperty("Driver");
            String url=info.getProperty("url");
            Class.forName(Driver);
            conn= DriverManager.getConnection(url,user,password);
            // 3.创建语句
            st = conn.createStatement();
            String sql = "select gName,gPrice,gNumber,gtName,gPhoto from goods join goodstype on goods.gType = goodstype.gtId";
//            System.out.println(sql);
            // 4.执行语句
            rs = st.executeQuery(sql);
            // 创建一个集合
            List<Good> list = new ArrayList<Good>();
            while (rs.next()) {
                Good stu = new Good(
                        rs.getString("gName"),
                        rs.getFloat("gPrice"),
                        rs.getInt("gNumber"),
                        rs.getString("gtName"),
                        rs.getString("gPhoto"));
                list.add(stu);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
